package DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Sibrand
 */
public class TripManager extends DB {

    public TripManager(String dbName, String userName, String password) {
        super(dbName, userName, password);
    }

    public TripManager(Connection c) {
        conn = c;
    }

    public int createNewTrip(String[] users) {
        if (users.length >= 1 && usernameExists(users[0])) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Trips (owner) VALUES (?)");
                pstmt.setString(1, users[0]);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("SELECT * FROM Trips WHERE owner= ? ORDER BY tripID DESC");
                pstmt.setString(1, users[0]);
                ResultSet rs = pstmt.executeQuery();
                rs.next();

                int tripID = rs.getInt(1);

                for (int i = 0; i < users.length && usernameExists(users[i]); ++i) {
                    pstmt = conn.prepareStatement("INSERT INTO TripOwners (tripID, owner, confirmed, processed, viewedByAskingUser) VALUES (?, ?, 1, 1, 1)");
                    pstmt.setString(2, users[i]);
                    pstmt.setInt(1, tripID);
                    pstmt.executeUpdate();
                }
                return tripID;

            } catch (SQLException ex) {
                return -1;
            }
        }
        return -1;
    }

    public boolean isOwnerOf(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Trips WHERE owner= ? AND tripID= ?");
                pstmt.setString(1, user);
                pstmt.setInt(2, tripId);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return true;
                }

                pstmt = conn.prepareStatement("SELECT * FROM TripOwners WHERE confirmed=1 AND processed=1 AND username= ?  AND tripID= ?");
                pstmt.setString(1, user);
                pstmt.setInt(2, tripId);
                rs = pstmt.executeQuery();

                if (rs.next()) {
                    return true;
                }
                return false;
            } catch (SQLException ex) {
                return false;
            }
        } else {
            return false;
        }
    }

    public boolean isFamilyOf(int tripId, String user) {
        String owners[] = getOwners(tripId);
        RelationManager rm = new RelationManager(conn);
        boolean output = false;

        if (owners != null && user != null) {
            for (int i = 0; !output && i < owners.length; i++) {
                output = output || (rm.getRelationBetween(owners[i], user).matches("FAMILY"));
            }
        }
        return output;
    }

    public boolean isFriendOf(int tripId, String user) {
        String owners[] = getOwners(tripId);
        RelationManager rm = new RelationManager(conn);
        boolean output = false;
        if (owners != null && user != null) {
            for (int i = 0; !output && i < owners.length; i++) {
                output = output || (rm.getRelationBetween(owners[i], user).compareTo("FRIEND") == 0);
            }
        }

        return output;
    }

    public String[] getOwners(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username FROM TripOwners WHERE confirmed=1 AND processed=1 AND tripID= ?");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }

                return result;
            } catch (SQLException ex) {
                return new String[0];
            }

        } else {
            return new String[0];
        }
    }

    public int getTripIdByName(String tripName) {
        if (tripName != null && isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT tripID FROM Trips WHERE tripName=?");
                pstmt.setString(1, tripName);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return rs.getInt(1);
                }
            } catch (SQLException ex) {
                return -1;
            }
        }
        return -1;
    }

    public int addTrip(String tripName, String owner, String start, String end) {
        if (tripName != null && owner != null && start != null && end != null && isConnected() && start.matches("\\d{4}\\/\\d{2}\\/\\d{2}") && end.matches("\\d{4}\\/\\d{2}\\/\\d{2}")) {
            try {
                int id;
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Trips (tripName, owner, start, end) VALUES (?,?,?,?)");
                pstmt.setString(1, tripName);
                pstmt.setString(2, owner);
                pstmt.setString(3, start);
                pstmt.setString(4, end);
                pstmt.executeUpdate();



                pstmt = conn.prepareStatement("SELECT * FROM Trips WHERE owner= ? ORDER BY tripID DESC");
                pstmt.setString(1, owner);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    id = rs.getInt(1);
                    pstmt = conn.prepareStatement("INSERT INTO TripOwners (tripID, username, confirmed, processed) VALUES (?,?,?,?)");
                    pstmt.setString(1, String.valueOf(id));
                    pstmt.setString(2, owner);
                    pstmt.setString(3, "1");
                    pstmt.setString(4, "1");
                    pstmt.executeUpdate();
                    return id;
                }
            } catch (SQLException ex) {
                return -1;
            }
        }
        return -1;
    }

    public boolean removeTrip(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM Trips WHERE tripID = ?");
                pstmt.setInt(1, tripId);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM Comments WHERE replyOnContentID IN (SELECT contentID FROM Contents WHERE containerID IN (SELECT containerID FROM ContentContainers WHERE tripID = ?))");
                pstmt.setInt(1, tripId);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM Contents WHERE containerID IN (SELECT containerID FROM ContentContainers WHERE tripID = ?)");
                pstmt.setInt(1, tripId);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM ContentContainers WHERE tripID = ?");
                pstmt.setInt(1, tripId);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM TripOwners WHERE tripID = ?");
                pstmt.setInt(1, tripId);
                pstmt.executeUpdate();

                return true;
            } catch (SQLException ex) {
                System.out.println("SQL exception " + ex.getMessage());
                return false;
            }
        } else {
            return false;
        }
    }

    public class intStringContainer {

        public int id;
        public String s;

        public intStringContainer(int id, String s) {
            this.id = id;
            this.s = s;
        }
    }

    public intStringContainer[] getOwnershipRequestsForOwner(String owner) {
        int tripIDs[] = getTripsOwnedBy(owner);

        intStringContainer result[] = new intStringContainer[0];

        for (int i = 0; i <
                tripIDs.length; ++i) {
            String temp[] = getOwnershipRequestsByTripID(tripIDs[i]);
            if (result == null && temp.length != 0) {
                result = new intStringContainer[temp.length];
                for (int j = 0; j <
                        temp.length; ++j) {
                    result[j] = new intStringContainer(tripIDs[i], temp[j]);
                }

            } else if (temp.length != 0) {
                intStringContainer[] temp2 = new intStringContainer[temp.length + result.length];
                System.arraycopy(temp2, 0, result, 0, result.length);
                for (int j = 0; j <
                        temp.length; ++j) {
                    temp2[j + result.length] = new intStringContainer(tripIDs[i], temp[j]);
                }

                result = temp2;
            }

        }
        if (result == null) // Make sure it always returns an array
        {
            result = new intStringContainer[0];
        }

        return result;
    }

    public String[] getOwnershipRequestsByTripID(int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username FROM TripOwners WHERE processed=0 AND tripID= ?");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }

                return result;
            } catch (SQLException ex) {
                return new String[0];
            }

        } else {
            return new String[0];
        }

    }

    public int[] getTripsOwnedBy(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT tripID FROM TripOwners WHERE confirmed=1 AND processed=1 AND username= ?");
                pstmt.setString(1, user);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }

                return result;
            } catch (SQLException ex) {
                return new int[0];
            }

        } else {
            return new int[0];
        }

    }

    public String getTripName(
            int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT tripName FROM Trips WHERE tripID= ?");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return rs.getString("tripName");
                } else {
                    return "";
                }

            } catch (SQLException ex) {
                return "";
            }

        } else {
            return "";
        }

    }

    public String getStart(
            int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT start FROM Trips WHERE tripID= ?");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return rs.getString("start");
                } else {
                    return "";
                }

            } catch (SQLException ex) {
                return "";
            }

        } else {
            return "";
        }

    }

    public String getEnd(
            int tripId) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT end FROM Trips WHERE tripID= ?");
                pstmt.setInt(1, tripId);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return rs.getString("end");
                } else {
                    return "";
                }

            } catch (SQLException ex) {
                return "";
            }

        } else {
            return "";
        }

    }

    public boolean changeTripName(int tripId, String name) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE Trips SET tripName= ? WHERE tripID= ?");
                pstmt.setString(1, name);
                pstmt.setInt(2, tripId);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }

        } else {
            return false;
        }

    }

    public boolean changeStart(int tripId, String start) {
        if (start != null) {
            if (isConnected() && start.matches("\\d{4}\\/\\d{2}\\/\\d{2}")) {
                try {
                    PreparedStatement pstmt = conn.prepareStatement("UPDATE Trips SET start= ? WHERE tripID = ?");
                    pstmt.setString(1, start);
                    pstmt.setInt(2, tripId);
                    pstmt.executeUpdate();
                    return true;
                } catch (SQLException ex) {
                    System.out.println("Change start failed: " + ex.getMessage());
                    return false;
                }

            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public boolean changeEnd(int tripId, String end) {
        if (end != null) {
            if (isConnected() && end.matches("\\d{4}\\/\\d{2}\\/\\d{2}")) {
                try {
                    PreparedStatement pstmt = conn.prepareStatement("UPDATE Trips SET end= ? WHERE tripID= ?");
                    pstmt.setString(1, end);
                    pstmt.setInt(2, tripId);
                    pstmt.executeUpdate();
                    return true;
                } catch (SQLException ex) {
                    return false;
                }

            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public boolean confirmOwnership(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE TripOwners SET confirmed=1, processed=1 WHERE username= ? AND tripID= ?");
                pstmt.setInt(2, tripId);
                pstmt.setString(1, user);
                pstmt.executeUpdate();
                return true;

            } catch (SQLException ex) {
                return false;
            }

        }
        return false;
    }

    public boolean declineOwnership(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE TripOwners SET confirmed=0, processed=1 WHERE username= ? AND tripID= ?");
                pstmt.setInt(2, tripId);
                pstmt.setString(1, user);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }

        }
        return false;
    }

    public boolean removeOwnership(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM TripOwners WHERE username= ? AND tripID= ?");
                pstmt.setInt(2, tripId);
                pstmt.setString(1, user);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }

        }
        return false;
    }

    public boolean makeOwnershipRequest(int tripId, String askingUser) {
        if (isConnected() && usernameExists(askingUser)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO TripOwners (tripID, username, confirmed) VALUES ( ? , ?, 0)");
                pstmt.setInt(1, tripId);
                pstmt.setString(2, askingUser);
                pstmt.executeUpdate();
                return true;
            } catch (SQLException ex) {
                return false;
            }

        }
        return false;
    }

    public int[] getTripsForEverybody(String user) {
        if (isConnected()) {
            try {
                ResultSet rs = null;
                if (user == null) {
                    PreparedStatement pstmt = conn.prepareStatement("SELECT DISTINCT tripID FROM ContentContainers WHERE viewableBy = \"EVERYBODY\"");
                    rs =
                            pstmt.executeQuery();
                } else {
                    PreparedStatement pstmt = conn.prepareStatement("SELECT DISTINCT tripID FROM ContentContainers WHERE viewableBy = \"EVERYBODY\" AND tripID NOT IN (SELECT tripID FROM TripOwners WHERE username = ?)");
                    pstmt.setString(1, user);
                    rs =
                            pstmt.executeQuery();
                }

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }

                return result;
            } catch (SQLException ex) {
                return new int[0];
            }

        } else {
            return new int[0];
        }

    }

    public int[] getTripsForRelations(String user) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT DISTINCT tripID FROM ContentContainers WHERE (viewableBy = \"FRIEND\" OR viewableBy = \"FAMILY\")AND" +
                        " tripID IN (SELECT tripID FROM TripOwners WHERE username != ? AND username IN ( SELECT username1 FROM Relations" +
                        " WHERE username2 = ? AND accepted =1 UNION SELECT username2 AS username1 FROM Relations WHERE" +
                        " username1 = ? AND accepted =1 ) )");
                pstmt.setString(1, user);
                pstmt.setString(2, user);
                pstmt.setString(3, user);
                ResultSet rs = pstmt.executeQuery();

                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }

                return result;
            } catch (SQLException ex) {
                return new int[0];
            }

        } else {
            return new int[0];
        }

    }

    public boolean isDeclinedOwnerOf(int tripId, String user) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM TripOwners WHERE tripID= ? AND username= ? AND confirmed=0 AND processed=1");
                pstmt.setInt(1, tripId);
                pstmt.setString(2, user);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return true;
                } else {
                    return false;
                }

            } catch (SQLException ex) {
                return false;
            }

        } else {
            return false;
        }

    }

    public boolean isUnprocessedOwnerOf(int tripId, String user) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM TripOwners WHERE tripID= ? AND username= ? AND processed=0");
                pstmt.setInt(1, tripId);
                pstmt.setString(2, user);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return true;
                } else {
                    return false;
                }

            } catch (SQLException ex) {
                return false;
            }

        } else {
            return false;
        }

    }

    public boolean isConfirmedOwnerOf(int tripId, String user) {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM TripOwners WHERE tripID= ? AND username= ? AND confirmed=1 AND processed=1");
                pstmt.setInt(1, tripId);
                pstmt.setString(2, user);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return true;
                } else {
                    return false;
                }

            } catch (SQLException ex) {
                return false;
            }

        } else {
            return false;
        }

    }

    public void viewed(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE TripOwners SET viewedByAskingUser=1 WHERE username= ? AND tripID= ?");
                pstmt.setString(1, user);
                pstmt.setInt(2, tripId);
                pstmt.executeUpdate();
            } catch (SQLException ex) {
            }
        }
    }

    public int[] getPendingOwnershipRequests(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT tripID FROM TripOwners WHERE viewedByAskingUser=0 AND username= ?");
                pstmt.setString(1, user);
                ResultSet rs = pstmt.executeQuery();
                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }

                return result;
            } catch (SQLException ex) {
                return new int[0];
            }

        } else {
            return new int[0];
        }

    }

    public int[] getLatestTrips() {
        if (isConnected()) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT tripID FROM Trips ORDER BY tripID DESC LIMIT 0,12");
                ResultSet rs = pstmt.executeQuery();
                int[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result =
                            new int[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getInt(1);
                        currentRow++;

                        if (rs.next()) {
                            rs.previous();
                            int[] temp = new int[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result =
                                    temp;
                        }

                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new int[0];
                }

                return result;
            } catch (SQLException ex) {
                return new int[0];
            }

        } else {
            return new int[0];
        }
    }

    public boolean isAccepted(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT confirmed FROM TripOwners WHERE username= ? AND tripID=?");
                pstmt.setString(1, user);
                pstmt.setInt(2, tripId);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getInt(1) == 1;
                } else {
                    return false;
                }

            } catch (SQLException ex) {
                return false;
            }

        } else {
            return false;
        }

    }

    public boolean isProcessed(int tripId, String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT processed FROM TripOwners WHERE username= ? AND tripID=?");
                pstmt.setString(1, user);
                pstmt.setInt(2, tripId);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getInt(1) == 1;
                } else {
                    return false;
                }

            } catch (SQLException ex) {
                return false;
            }

        } else {
            return false;
        }
    }
}

